create database OADatabase

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmenInfot') and o.name = 'FK_DEPARTME_REFERENCE_EMPLOYEE')
alter table DepartmenInfot
   drop constraint FK_DEPARTME_REFERENCE_EMPLOYEE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_USERINFO')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserInfo') and o.name = 'FK_USERINFO_REFERENCE_USERAPPL')
alter table UserInfo
   drop constraint FK_USERINFO_REFERENCE_USERAPPL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmenInfot')
            and   type = 'U')
   drop table DepartmenInfot
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeInfo')
            and   type = 'U')
   drop table EmployeeInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplyInfo')
            and   type = 'U')
   drop table UserApplyInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: DepartmenInfot                                        */
/*==============================================================*/
create table DepartmenInfot (
   dp_Id                int                  identity,
   em_Id                int                  null,
   dp_Name              nvarchar(20)         null,
   dp_competent         nvarchar(20)         null,
   dp_phone             nvarchar(30)         null,
   dp_email             nvarchar(20)         null,
   constraint PK_DEPARTMENINFOT primary key (dp_Id)
)
go

/*==============================================================*/
/* Table: EmployeeInfo                                          */
/*==============================================================*/
create table EmployeeInfo (
   em_Id                int                  identity,
   u_Id                 int                  null,
   em_name              nvarchar(20)         null,
   em_position          nvarchar(20)         null,
   em_contact           nvarchar(20)         null,
   em_pay               money                null,
   constraint PK_EMPLOYEEINFO primary key (em_Id)
)
go

/*==============================================================*/
/* Table: UserApplyInfo                                         */
/*==============================================================*/
create table UserApplyInfo (
   "Ua_ number"         int                  identity,
   u_Id                 int                  null,
   Ua_date              date                 null,
   Ua_fate              nvarchar(40)         null,
   Ua_people            nvarchar(40)         null,
   Ua_site              nvarchar(50)         null,
   Ua_budget            money                null,
   Ua_accept            nvarchar(40)         null,
   constraint PK_USERAPPLYINFO primary key ("Ua_ number")
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   u_Id                 int                  not null,
   "Ua_ number"         int                  null,
   u_Name               nvarchar(30)         null,
   u_bank               nvarchar(50)         null,
   u_phone              nvarchar(30)         null,
   u_address            nvarchar(50)         null,
   constraint PK_USERINFO primary key (u_Id)
)
go

alter table DepartmenInfot
   add constraint FK_DEPARTME_REFERENCE_EMPLOYEE foreign key (em_Id)
      references EmployeeInfo (em_Id)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_USERINFO foreign key (u_Id)
      references UserInfo (u_Id)
go

alter table UserInfo
   add constraint FK_USERINFO_REFERENCE_USERAPPL foreign key ("Ua_ number")
      references UserApplyInfo ("Ua_ number")
go
